package tech.jabari.utils;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * JDBC操作的工具类。
 * @author Jabari Lu
 * @title: JdbcUtil
 * @data 2020/7/30 14:22
 */
public class JdbcUtil {

    private static  final String      URL               = "jdbc:mysql://localhost:3306/exercise02?characterEncoding=UTF-8";// 数据库连接的URL todo:
    private static  final String      DRIVER            = "com.mysql.jdbc.Driver";// 数据库连接的驱动名
    private static  final String      USER              = "root";// 数据库的用户名
    private static  final String      MYSQL_PWD         = "123456";// 数据库的密码
    private static  Connection        conn              = null;// 数据库连接Connection对象
    private static  PreparedStatement preparedStatement = null;// 预编译语句PreparedStatement对象

    static {
        getConnection();
    }

    public static Connection getConnection(){
        //1.加载MySQL的驱动
        try {
            Class.forName(DRIVER);
        } catch (ClassNotFoundException e) {
            throw new RuntimeException("加载MySQL驱动失败："+e);
        }
        try {
            //2.获取数据库的连接Connection对象
            conn = DriverManager.getConnection(URL, USER, MYSQL_PWD);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        System.out.println("加载MySQL驱动成功！");
        return conn;
    }


    public static int update(String sql,Object ...params) {
        int paramSize = judgeParamSizeMatch(sql, params, "update()");
        int result = -1;
        try {
            preparedStatement = conn.prepareStatement(sql);
            //给预编译语句赋值
            for (int i = 0; i < paramSize; i++) {
                preparedStatement.setObject(i+1,params[i]);
            }
            //执行SQL语句
            result = preparedStatement.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }

        return result;
    }

    private static int judgeParamSizeMatch(String sql, Object[] params, String s) {
        int cnt = getParameterCnt(sql);
        int paramSize = null == params ? 0 : params.length;
        if (cnt != paramSize) {
            throw new RuntimeException(s+"参数个数和参数值不匹配！");
        }
        return paramSize;
    }

    public static List query(String sql, Object ...params){
        int paramSize = judgeParamSizeMatch(sql, params, "query()");
        List result = null;
        try {
            preparedStatement = conn.prepareStatement(sql);
            //给预编译语句赋值
            for (int i = 0; i < paramSize; i++) {
                preparedStatement.setObject(i+1,params[i]);
            }
            //执行SQL语句
            ResultSet rs = preparedStatement.executeQuery();
            //封装查询结果
            ResultSetMetaData rsmd = rs.getMetaData();
            int columnNum = rsmd.getColumnCount();
            while(rs.next()){
                if (result == null) {
                    result = new ArrayList();
                }
                Map<String, Object> dataMap = new HashMap<String, Object>(0);
                for(int i=1;i<=columnNum;i++){
                    dataMap.put(rsmd.getColumnName(i), rs.getObject(i));
                }
                result.add(dataMap);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return result;
    }

    public static void close() {
        closeStat();
        closeConn();
    }


    public static void closeConn(){
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
    }

    public static void closeStat(){
        if (preparedStatement != null) {
            try {
                preparedStatement.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
    }


    /**
     * 获取sql参数中的？个数。
     * @param sql - String
     * @return int - 没有？则返回0
     */
    private static int getParameterCnt(String sql) {
        int cnt = 0;
        if (sql == null || "".equals(sql.trim())) {
            return cnt;
        }
        // 正则表达式 实现
        /*Pattern pattern = Pattern.compile("\\?");
        Matcher m = pattern.matcher(sql);
        while (m.find()) {
            cnt++;
        }*/
        // for循环实现
        int sqlLen = sql.length();
        for (int i = 0; i < sqlLen; i++) {
            if(sql.charAt(i) == '?'){
                cnt++;
            }
        }
        return cnt;
    }

    public static void main(String[] args) {
        /*String sql = "insert into t_user(username,pwd,sex,birth) values(?,?,?,?)";
        int cnt = getParameterCnt(sql);
        System.out.println(cnt);*/
        /*System.out.println(System.getProperty("java.class.path"));*/
        // JUNIT
        String sql = "select * from t_user where id in (1,2)";
        List queryList = query(sql, null);
        System.out.println("----------query ok!");
    }




}
